%%%-------------------------------------------------------------------
%%% @author chenlong
%%% @copyright (C) 2020, <COMPANY>
%%% @doc
%%% 具体执行更新数据库的函数
%%% @end
%%% Created : 18. 8月 2020 10:58
%%%-------------------------------------------------------------------
-module(update_db_util).
-author("chenlong").

-include("common.hrl").

-define(APP,[crypto, emysql]).

%%关键字类型
-define(SQL_TYPE_CREATE_TABLE, 1).
-define(SQL_TYPE_ADD_COLUMN, 2).
-define(SQL_TYPE_MODIFY_COLUMN, 3).
-define(SQL_TYPE_DELETE_DATA, 4).
-define(SQL_TYPE_UPDATE_DATA, 5).

-define(NEED_CHECK_DUPLICATE_SQL_TYPE_LIST, [?SQL_TYPE_CREATE_TABLE, ?SQL_TYPE_ADD_COLUMN, ?SQL_TYPE_MODIFY_COLUMN]).

%% API
-export([update_db/0, write_log_file/1, write_log_file/2, delete_log_file/0]).


%%返回执行成功还是失败
%%return boolean()
update_db() ->
	delete_log_file(),
	write_log_file("update_db begin"),
	%%先检查SQL语句
	R = case format_sql_list() of
		{ok, FormatSqlList} ->
			SuccAppList = tk_misc:start_applications(?APP),
			case lists:member(emysql,SuccAppList) of
				?TRUE ->
					{IP, Port, Name, Pass, DBName, Num} = data_setting:get(database),
					ok = emysql:add_pool(?DB, Num, Name, Pass, IP, Port, DBName, utf8);
				_ -> ok
			end,
			do_update_db(FormatSqlList);
		_ -> ?FALSE
	end,
	tk_misc:stop_applications(?APP),
	case R of
		?TRUE ->
			DBName1 = element(5,data_setting:get(database)),
			write_log_file(io_lib:format("~s update_db success",[DBName1]));
		_ -> write_log_file("update_db fail, check the log file")
	end,
	R.

%%返回执行成功还是失败
%%return boolean()
do_update_db(FormatSqlList) ->
	ExecuteSqlList = calc_execute_sql_list(FormatSqlList),
	util:listFunReturn(fun(Sql) ->
		case db_sql:sql_execute_with_log(Sql) of
			{ok, _} -> {?TRUE,?TRUE};
			_ ->
				write_log_file("execute sql fail Sql=~p",[Sql]),
				{?FALSE,?FALSE}
		end
	                   end, ?FALSE, ExecuteSqlList, ?TRUE).

%%格式化SQL语句，得到语句中的关键字
%%检查SQL语句格式是否正确
%%return {ok,FormatSqlList}|fail
format_sql_list() ->
	write_log_file("format_sql_list begin"),
	%%1、新建表
	{ok, CreateTableRE} = re:compile("create table \`?(\\w+)\`?", [unicode, caseless]),
	%%2、增加字段
	{ok, AddColumnRE} = re:compile("ALTER table \`?(\\w+)\`? ADD COLUMN \`?(\\w+)\`? ([^ ]+( unsigned)?) .*after", [unicode, caseless]),
	%%3、修改字段
	{ok, ModifyColumnRE} = re:compile("ALTER table \`?(\\w+)\`? modify COLUMN \`?(\\w+)\`? ([^ ]+( unsigned)?)", [unicode, caseless]),
	%%4、删除数据
	{ok, DeleteDataRE} = re:compile("DELETE FROM \`?(\\w+)\`?", [unicode, caseless]),
	%%5、修改数据
	{ok, UpdateDataRE} = re:compile("update \`?(\\w+)\`? set", [unicode, caseless]),
	SqlList = util:format_utf8(data_update_sql:get(sql_list)),
	FormatSqlList = util:foldl(fun(Sql, AccList) ->
		case calcKeyWord(Sql, CreateTableRE, AddColumnRE, ModifyColumnRE, DeleteDataRE, UpdateDataRE) of
			{ok, {SqlType, KeyTable, KeyWord, KeyWordType}} ->
				case lists:member(SqlType, ?NEED_CHECK_DUPLICATE_SQL_TYPE_LIST) of
					?TRUE ->
						case lists:keymember({SqlType, KeyTable, KeyWord}, 1, AccList) of
							?TRUE ->%%有重复的了
								write_log_file("format_sql_list duplicate Sql=~s", [Sql]),
								{return, fail};
							_ ->
								[{{SqlType, KeyTable, KeyWord}, KeyWordType, Sql} | AccList]
						end;
					_ ->
						[{{SqlType, KeyTable, KeyWord}, KeyWordType, Sql} | AccList]
				end;
			_ ->
				write_log_file("calcKeyWord fail Sql=~s",[Sql]),
				{return, fail}
		end
	                           end, [], SqlList),
	write_log_file("format_sql_list finish"),
	?IF(is_list(FormatSqlList), {ok,lists:reverse(FormatSqlList)}, fail).

%%将SQL语句和数据库已经存在的结构比较，得到需要执行的SQL
%%return SqlList
calc_execute_sql_list(FormatSqlList) ->
	write_log_file("calc_execute_sql_list begin"),
	SqlList = lists:foldr(
		fun(FormatSql, AccList) ->
			case calc_need_execute(FormatSql) of
				?TRUE -> [element(3, FormatSql) | AccList];
				_ -> AccList
			end
		end, [], FormatSqlList),
	write_log_file("calc_execute_sql_list finish"),
	SqlList.


%%===================单独的更新数据库-日志文件===========================
%%根据版本号来命名
write_log_file(F, A) ->
	LogStr = io_lib:format(F, A),
	write_log_file(LogStr).
write_log_file(LogStr0) ->
	LogStr = io_lib:format("~s~n", [LogStr0]),
	io:format(LogStr),
	{M,S,L} = data_setting:get(client_version),
	FileName = io_lib:format("update_db_~w_~w_~w.txt",[M,S,L]),
	file:write_file(FileName,LogStr,[append]).

delete_log_file() ->
	{M,S,L} = data_setting:get(client_version),
	FileName = io_lib:format("update_db_~w_~w_~w.txt",[M,S,L]),
	file:delete(FileName).

%%===================单独的更新数据库-日志文件 END===========================

%%============================LOCAL FUNCTION==================================
%%return {SqlType,KeyTable,KeyWord,KeyWordType}
calcKeyWord(Sql, CreateTableRE, AddColumnRE, ModifyColumnRE, DeleteDataRE, UpdateDataRE) ->
	try
		case re:run(Sql, CreateTableRE, [global, {capture, [1], list}]) of
			{match, [[TableStr1]]} ->
				throw({ok, {?SQL_TYPE_CREATE_TABLE, TableStr1, "", ""}});
			_ -> ok
		end,
		case re:run(Sql, AddColumnRE, [global, {capture, [1, 2, 3], list}]) of
			{match, [[KeyTable2, KeyWord2, KeyWordType2]]} ->
				throw({ok, {?SQL_TYPE_ADD_COLUMN, KeyTable2, KeyWord2, KeyWordType2}});
			_ -> ok
		end,
		case re:run(Sql, ModifyColumnRE, [global, {capture, [1, 2, 3], list}]) of
			{match, [[KeyTable3, KeyWord3, KeyWordType3]]} ->
				throw({ok, {?SQL_TYPE_MODIFY_COLUMN, KeyTable3, KeyWord3, KeyWordType3}});
			_ -> ok
		end,
		case re:run(Sql, DeleteDataRE, [global, {capture, [1], list}]) of
			{match, [[TableStr4]]} ->
				throw({ok, {?SQL_TYPE_DELETE_DATA, TableStr4, "", ""}});
			_ -> ok
		end,
		case re:run(Sql, UpdateDataRE, [global, {capture, [1], list}]) of
			{match, [[TableStr5]]} ->
				throw({ok, {?SQL_TYPE_UPDATE_DATA, TableStr5, "", ""}});
			_ -> ok
		end,
		fail
	catch
		Ret -> Ret
	end.

calc_need_execute({{?SQL_TYPE_CREATE_TABLE, KeyTable, _KeyWord}, _KeyWordType, _Sql}) ->
	case db_sql:get_row(io_lib:format("show tables like \'~s\';", [KeyTable])) of
		[] -> ?TRUE;
		_ -> ?FALSE%%已经存在该表了
	end;
calc_need_execute({{?SQL_TYPE_ADD_COLUMN, KeyTable, KeyWord}, _KeyWordType, _Sql}) ->
	ColumnList0 = db_sql:get_rows(io_lib:format("desc ~s;", [KeyTable])),
	ColumnList = [erlang:list_to_tuple(Column) || Column <- ColumnList0],
	KeyWordBin = erlang:list_to_binary(KeyWord),
	case lists:keymember(KeyWordBin, 1, ColumnList) of
		?TRUE -> ?FALSE;%%已经有该字段了
		_ -> ?TRUE
	end;
calc_need_execute({{?SQL_TYPE_MODIFY_COLUMN, KeyTable, KeyWord}, KeyWordType, Sql}) ->
	ColumnList0 = db_sql:get_rows(io_lib:format("desc ~s;", [KeyTable])),
	ColumnList = [erlang:list_to_tuple(Column) || Column <- ColumnList0],
	KeyWordBin = erlang:list_to_binary(KeyWord),
	KeyWordTypeBin = erlang:list_to_binary(KeyWordType),
	case lists:keytake(KeyWordBin, 1, ColumnList) of
		{value, ColumnTuple, _T} ->%%有这个字段，可以进行修改
			%%判断字段类型是否已经一样了
			case element(2, ColumnTuple) of
				KeyWordTypeBin -> ?FALSE;%%已经一样了
				_ -> ?TRUE
			end;
		_ ->%%没有该字段，但是想修改
			write_log_file("calc_need_execute no column but to modify Sql=~s", [Sql]),
			?FALSE
	end;
calc_need_execute({{?SQL_TYPE_DELETE_DATA, KeyTable, _KeyWord}, _KeyWordType, Sql}) ->
	case db_sql:get_row(io_lib:format("show tables like \'~s\';", [KeyTable])) of
		[] ->
			write_log_file("calc_need_execute no table Sql=~s",[Sql]);%%没有这张表
		_ -> ok
	end,
	?TRUE;
calc_need_execute({{?SQL_TYPE_UPDATE_DATA, KeyTable, _KeyWord}, _KeyWordType, Sql}) ->
	case db_sql:get_row(io_lib:format("show tables like \'~s\';", [KeyTable])) of
		[] ->
			write_log_file("calc_need_execute no table Sql=~s",[Sql]);%%没有这张表
		_ -> ok
	end,
	?TRUE.